/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package monitoring.dbpersistence;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import monitoring.beans.database.*;
import monitoring.beans.util.FilterBean;
import monitoring.beans.util.ImportMessage;
import monitoring.dbpersistence.util.DBConnection;
import monitoring.dbpersistence.util.SQLProcessing;
import org.joda.time.field.ImpreciseDateTimeField;

/**
 *
 * @author Regulus
 */
public class MonitoringDBPersistence {

    public List<MonitoringDB> getList(List<FilterBean> filter) throws SQLException {
        //criação da lista
        List<MonitoringDB> bean = new ArrayList<MonitoringDB>(); //filter =bean
        //conectar ao DB
        SQLProcessing sql = new SQLProcessing();
        String line = sql.select(filter);

        DBConnection dbc = new DBConnection();
        //executar o sql

        ResultSet rs = dbc.executeSQL(line, true);

        while (rs.next()) {
            MonitoringDB mdb = new MonitoringDB();
            TaxonomicElement tx = new TaxonomicElement();
            mdb.setTaxonomicElement(tx);
            GeospatialElement ge = new GeospatialElement();
            mdb.setGeospatialElement(ge);
            LocalityElement le = new LocalityElement();
            mdb.setLocalityElement(le);
            OccurrenceElement occ = new OccurrenceElement();
            mdb.setOcurrenceElement(occ);
            RecordLevelElement rec = new RecordLevelElement();
            mdb.setRecordLevelElement(rec);
            
            
            mdb.setInstallationDate(rs.getDate("installationdate"));            
            mdb.setInstallationTime(rs.getTimestamp("installationtime"));
            mdb.setCollectDate(rs.getDate("collectdate"));
            mdb.setCollectTime(rs.getTimestamp("collecttime"));
            mdb.setPlotNumber(rs.getInt("plotnumber"));
            mdb.setAmostralNumber(rs.getInt("amostralnumber"));
            mdb.setFloorheight(rs.getBigDecimal("floorheight"));
            mdb.setWeight(rs.getBigDecimal("weight"));
            mdb.setWidth(rs.getBigDecimal("width"));
            mdb.setLength(rs.getBigDecimal("length"));
            mdb.setHeight(rs.getBigDecimal("height"));
            mdb.getSurroundingsVegetation().setSurroundingsVegetation(rs.getString("surroundingsvegetation"));
            //mdb.setTipoDeSuporte(rs.getString("tipodesuporte"));
            mdb.setIdGeral(rs.getString("idgeral"));
            
            mdb.setDenomination(new Denomination(null, rs.getString("denomination")));
            mdb.setTechnicalCollection(new Technicalcollection(null, rs.getString("technicalCollection")));
            mdb.setDigitizer(new Digitizer(null, rs.getString("digitizer")));
            mdb.setCulture(new Culture(null, rs.getString("culture")));
            mdb.setCultivar(new Cultivar(null,rs.getString("cultivar")));
            mdb.setPredominantBiome(new PredominantBiome(null, rs.getString("predominantbiome")));
            mdb.setSurroundingsCulture(new SurroundingsCulture(null, rs.getString("surroundingsculture")));
            mdb.setColorPanTrap(new ColorPanTrap(null, rs.getString("colorpantrap")));
            mdb.setSupportType(new SupportType(null,rs.getString("supporttype")));
            mdb.setCollector(new Collector(null, rs.getString("collector")));
           
            mdb.getGeospatialElement().setDecimalLatitude(rs.getBigDecimal("decimallatitude"));
            mdb.getGeospatialElement().setDecimalLongitude(rs.getBigDecimal("decimallongitude"));
            mdb.getGeospatialElement().setGeodeticDatum(rs.getString("geodeticdatum"));
            mdb.getGeospatialElement().setReferencePoints(rs.getString("referencepoints"));
            
            mdb.getLocalityElement().setCoordinatePrecision(rs.getString("coordinateprecision"));
            mdb.getLocalityElement().setCountry(new Country(null, rs.getString("country")));
            mdb.getLocalityElement().setLocality(new Locality(null, rs.getString("locality")));
            mdb.getLocalityElement().setLocationRemark(rs.getString("locationremark"));
            mdb.getLocalityElement().setMunicipality(new Municipality(null, rs.getString("municipality")));
            mdb.getLocalityElement().setStateProvince(new StateProvince(null, rs.getString("stateprovince")));
            mdb.getLocalityElement().setVerbatimElevation(rs.getString("verbatimelevation"));
            
            mdb.getTaxonomicElement().setFamily(new Family(null, rs.getString("family")));
            mdb.getTaxonomicElement().setGenus(new Genus(null,rs.getString("genus")));
            mdb.getTaxonomicElement().setOrder(new Order(null,rs.getString("order")));
            mdb.getTaxonomicElement().setScientificName(new ScientificName(null, rs.getString("scientificname")));
            mdb.getTaxonomicElement().setSubGenus(new SubGenus(null, rs.getString("subgenus")));
            mdb.getTaxonomicElement().setVernacularName(rs.getString("vernacularname"));
            mdb.getTaxonomicElement().setSpeciesName(new SpeciesName(null,rs.getString("speciesname")));
            mdb.getTaxonomicElement().setSubSpecies(new SubSpecies(null,rs.getString("subspecies")));
            mdb.getTaxonomicElement().setTribe(new Tribe(null,rs.getString("tribe")));
            mdb.getTaxonomicElement().setSubTribe(new SubTribe(null,rs.getString("subtribe")));
            
            mdb.getRecordLevelElement().setBasisOfRecord(new BasisOfRecord(null, rs.getString("basisofrecord")));
            mdb.getRecordLevelElement().setCollectionCode(new CollectionCode(null, rs.getString("collectioncode")));
            mdb.getRecordLevelElement().setInstitutionCode(new InstitutionCode(null, rs.getString("institutioncode")));
            
            
            mdb.getOcurrenceElement().setCatalogNumber(rs.getString("catalognumber"));
            mdb.getOcurrenceElement().setSex(new Sex(null, rs.getString("sex")));
            
            bean.add(mdb);
        }
        return bean;
    }

    public List<ImportMessage> persist(List<MonitoringDB> list) throws SQLException {
        int i = 0;
        List<ImportMessage> msgs = new ArrayList<ImportMessage>();
        StringBuffer sql = new StringBuffer("begin; ");
        SQLProcessing p = new SQLProcessing();
        DBConnection dbc;
        
        for(MonitoringDB m:list){
            m = this.getIdsByGUI(m);            
            i++;
            if(m.getIdMonitoring()==null){
                sql.append(p.insert(m));
                StringBuffer tmsg = new StringBuffer("Registro referente a linha ").append(m.getRow()).append(" da tabela foi inserido corretamente.");
                ImportMessage msg = new ImportMessage(ImportMessage.TYPE_INFORMATIVE, tmsg.toString(), ImportMessage.IMPL_SAVE, m.getRow(), "Monitoramento");
                msgs.add(msg);
            }else{
                sql.append(p.update(m));
                StringBuffer tmsg = new StringBuffer("Registro referente a linha ").append(m.getRow()).append(" da tabela foi atualizado corretamente.");
                ImportMessage msg = new ImportMessage(ImportMessage.TYPE_INFORMATIVE, tmsg.toString(), ImportMessage.IMPL_UPDATE, m.getRow(), "Monitoramento");
                msgs.add(msg);
            }
            
            if (i==100){
                sql.append(" commit;");
                
                dbc = new DBConnection();
                dbc.executeSQL(sql.toString(),false);
                dbc.closeConn();
                
                i=0;
                sql = new StringBuffer("begin; ");                
            }
        }        
        sql.append(" commit;");
        dbc = new DBConnection();
        //System.out.println(sql.toString());
        //System.exit(0);
        if(sql.toString().length()>25){
            dbc.executeSQL(sql.toString(),false);
        }
            
        dbc.closeConn();
        
        return msgs;
    }

    private MonitoringDB getIdsByGUI(MonitoringDB m) throws SQLException {        
        StringBuffer sql = new StringBuffer("SELECT idmonitoring, idgeospatialelement, m.idrecordlevelelement, idlocalityelement, idtaxonomicelement, m.idoccurrenceelement FROM monitoring");
        sql.append(" m LEFT JOIN recordlevelelement rec ON m.idrecordlevelelement = rec.idrecordlevelelement").
                append(" LEFT JOIN institutioncode ON rec.idinstitutioncode = institutioncode.idinstitutioncode").
                append(" LEFT JOIN collectioncode ON rec.idcollectioncode = collectioncode.idcollectioncode").
                append(" LEFT JOIN occurrenceelement ocrr ON m.idoccurrenceelement = ocrr.idoccurrenceelement").
                append(" WHERE UPPER(institutioncode)=UPPER('").append(m.getRecordLevelElement().getInstitutionCode().getInstitutionCode()).append("')").
                append(" AND UPPER(collectioncode)=UPPER('").append(m.getRecordLevelElement().getCollectionCode().getCollectionCode()).append("')").
                append(" AND UPPER(catalognumber)=UPPER('").append(m.getOcurrenceElement().getCatalogNumber()).append("')");
        DBConnection dbc = new DBConnection();        
        ResultSet rs = dbc.executeSQL(sql.toString(), true);                   
        while (rs.next()) {
            m.setIdMonitoring(rs.getInt("idmonitoring"));
            m.getGeospatialElement().setIdGeospatialElement(rs.getInt("idgeospatialelement"));
            m.getRecordLevelElement().setIdRecordLevelElement(rs.getInt("idrecordlevelelement"));
            m.getLocalityElement().setIdLocalityElement(rs.getInt("idlocalityelement"));
            m.getTaxonomicElement().setIdTaxonomicElement(rs.getInt("idtaxonomicelement"));
            m.getOcurrenceElement().setIdOccurrenceElement(rs.getInt("idoccurrenceelement"));
        }
        return m;
    }
}
